package jdbcRflect;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.sun.javafx.scene.control.GlobalMenuAdapter;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import javax.swing.*;
import java.beans.beancontext.BeanContext;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.nio.channels.ClosedSelectorException;
import java.rmi.MarshalledObject;
import java.sql.*;
import java.util.*;

public class JdbcTools {

    /**
     * 1.完成数据库链接操作
     */
    public static Connection getConnection() throws SQLException {

        InputStream resourceAsStream = JdbcTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        DataSource dataSource = null;
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection connection = dataSource.getConnection();
        return  connection;
    }

    public static void main(String[] args) {
        try {
            System.out.println("JdbcTools.getConnection() = " + JdbcTools.getConnection());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 2获取完整的预编译好的sql对象
     * @param sql
     * @param connection
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static PreparedStatement getPreparedStatement(String sql,Connection connection,Object...parameters) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(sql);
        //获取元数据
        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
        //获取字符串需要的参数个数
        int count = parameterMetaData.getParameterCount();
        //判断是否传参，并且设置参数值
        if (count!=0 && parameters !=null&& parameters.length==count)
            for (int i=0;i<count;i++){
                statement.setObject(i+1,parameters[i]);
            }
            return statement;
        }


//关闭对象释放资源
//        public static void close(Connection connection,PreparedStatement statement,ResultSet resultSet){
//            try {
//                connection.close();
//                statement.close();
//                resultSet.close();
//            } catch (SQLException throwables) {
//                throwables.printStackTrace();
//            }
//        }
//
//        public static void close(PreparedStatement statement,ResultSet resultSet){
//            try {
//                statement.close();
//                resultSet.close();
//            } catch (SQLException throwables) {
//                throwables.printStackTrace();
//            }
//        }
//    public static void close(Connection connection){
//        try {
//            connection.close();
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//    }


    /**
     *
     * 3实时更新，根据数据修改数据
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
        public static int update1(String sql,Object...parameters) throws SQLException {
            Connection connection = JdbcTools.getConnection();
            PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
            int i = preparedStatement.executeUpdate();
            return i;
        }

    /**
     * 4.关闭对象，释放资源
     *
     * @param resources
     */
    public static  void closeAll(AutoCloseable...resources){
        if (resources!=null && resources.length>0)
        {
            Arrays.stream(resources).forEach(source->{
                if (source!=null){
                    System.out.println("source = " + source);
                    try{
                        source.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            });
        }
    }

    /**
     *
     * 5根据条件查询一条数据，以传入的javaBean类型返回
     * @param sql
     * @param cls
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
        public static <T> T queryBean(String sql,Class<T> cls,Object...parameters) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
        //1定义需要的变量
            Connection connection =null;
            PreparedStatement preparedStatement =null;
            ResultSet resultSet = null;
            T t =null;
            //获取链接
            connection = JdbcTools.getConnection();
            //获取sql的预编译对象
            preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
            //4执行查询，得到结果集
            resultSet = preparedStatement.executeQuery();

            //5反编译对象，从结果集中获取标的原数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            System.out.println("metaData = " + metaData);

            while (resultSet.next()){
                t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
                int count = metaData.getColumnCount();//得到有几个标签
                for (int i = 1; i <=count ; i++) {
                    BeanUtils.setProperty(t,metaData.getColumnName(i),resultSet.getObject(i));
                }
            }
            JdbcTools.closeAll(connection,preparedStatement,resultSet);
            return t;
        }

    /**
     *
     * 6查询表中所有数据
     * @param sql
     * @param cls
     * @param paramenterst
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
        public static <T>List<T> queryBeanList(String sql, Class<T> cls,Object...paramenterst) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
            //1定义需要的变量
            Connection connection =null;
            PreparedStatement preparedStatement =null;
            ResultSet resultSet = null;
            ArrayList<T> arrayList = new ArrayList<>();
            T t =null;
            //获取链接
            connection = JdbcTools.getConnection();
            //获取sql的预编译对象
            preparedStatement = JdbcTools.getPreparedStatement(sql, connection, paramenterst);
            //4执行查询，得到结果集
            resultSet = preparedStatement.executeQuery();
            //5反编译对象，从结果集中获取标的原数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            System.out.println("metaData = " + metaData);
            while (resultSet.next()){
                t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
                int count = metaData.getColumnCount();//得到有几个标签
                for (int i = 1; i <=count ; i++) {
                    BeanUtils.setProperty(t,metaData.getColumnName(i),resultSet.getObject(i));
                }
                arrayList.add(t);
            }
            JdbcTools.closeAll(connection,preparedStatement,resultSet);
            return arrayList;
        }

    /**
     * 7查询多条记录，以Map集合的形势返回
     *
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
        public static ArrayList<Map> queryMapList(String sql,Object...parameters) throws SQLException {
            //1定义需要的变量
            Connection connection =null;
            PreparedStatement preparedStatement =null;
            ResultSet resultSet = null;
            ArrayList<Map> maps = new ArrayList<>();
            //获取链接
            connection = JdbcTools.getConnection();
            //获取sql的预编译对象
            preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
            //4执行查询，得到结果集
            resultSet = preparedStatement.executeQuery();
            //5反编译对象，从结果集中获取标的原数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count =metaData.getColumnCount();
            while (resultSet.next()){
                HashMap<Object, Object> map = new HashMap<>();
                for (int i = 1; i <= count; i++) {
                    map.put(metaData.getColumnName(i),resultSet.getObject(i));
                }
                maps.add(map);
            }
            JdbcTools.closeAll(connection,preparedStatement,resultSet);
            return maps;
        }

    /**
     *8 查询一条记录，以键值对形式返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Map queryMap(String sql,Object...parameters) throws SQLException {
        //1定义需要的变量
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;

        //获取链接
        connection = JdbcTools.getConnection();
        //获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5反编译对象，从结果集中获取标的原数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count =metaData.getColumnCount();
        HashMap<Object, Object> map = new HashMap<>();
        while (resultSet.next()){
            for (int i = 1; i <= count; i++) {
                map.put(metaData.getColumnName(i),resultSet.getObject(i));
            }
        }
        JdbcTools.closeAll(connection,preparedStatement,resultSet);
        return map;
    }


    /**
     * 9查询一条记录，以数组形式返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static Object[] queryArray(String sql,Object...parameters) throws SQLException {
        //1定义需要的变量
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;

        //获取链接
        connection = JdbcTools.getConnection();
        //获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5反编译对象，从结果集中获取标的原数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count =metaData.getColumnCount();
        Object[] o =new Object[count];//定义长度为count 的空数组
        resultSet.next();
            for (int i = 0; i < count; i++) {
                o[i] = resultSet.getObject(i+1);
            }
        JdbcTools.closeAll(connection,preparedStatement,resultSet);
        return o;
    }

    /**
     * 10查询多条记录，以数组集合（集合的数据是数组）的数据状态返回
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static List<Object[]> queryArrayList(String sql,Object...parameters) throws SQLException {
        //1定义需要的变量
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;
        ArrayList<Object[]> objects  = new ArrayList<>();

        //获取链接
        connection = JdbcTools.getConnection();
        //获取sql的预编译对象
        preparedStatement = JdbcTools.getPreparedStatement(sql, connection, parameters);
        //4执行查询，得到结果集
        resultSet = preparedStatement.executeQuery();
        //5反编译对象，从结果集中获取标的原数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count =metaData.getColumnCount();
        while (resultSet.next()){
            Object[] o =new Object[count];//定义长度为count 的空数组
            for (int i = 0; i < count; i++) {
                o[i] = resultSet.getObject(i+1);
            }
            objects.add(o);
        }
        JdbcTools.closeAll(connection,preparedStatement,resultSet);
        return objects;
    }



}
